/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package db;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import models.Customer;
import models.Employee;
import models.Order;
import models.OrderItem;
import models.Product;

/**
 *
 * @author pelle
 */
public class OrderDb extends AbstractDb implements OrderInterface {

    public Order getOrder(int orderId) {
        Order order = null;
        try {
            ResultSet rs = this.db.query(""
                + "SELECT * "
                + "FROM Orders "
                + "WHERE order_id = " + orderId);
            
            if (rs != null && rs.next()) {
                order = new Order();

                order.setId(rs.getInt("order_id"));
                order.setAmount(rs.getInt("order_amount"));
                order.setTotalPrice(rs.getFloat("order_id"));
                order.setDeliveryStatus(rs.getInt("order_deliveryStatus"));
                order.setDeliveryDate(rs.getString("order_deliveryDate"));
                order.setDate(rs.getString("order_date"));

                order.setCustomer(new CustomerDB().getCustomer(rs.getInt("order_customerid")));
                order.setEmployee(new EmployeeDB().getEmployee(rs.getInt("order_employeeid")));

                order.setOrderItems(this.getOrderItems(order));

            }
        } catch (SQLException ex) {
            Logger.getLogger(OrderDb.class.getName()).log(Level.SEVERE, null, ex);
        }

        return order;

    }

    private ArrayList<OrderItem> getOrderItems(Order order) {
        ArrayList<OrderItem> orderItems = new ArrayList<OrderItem>();
        try {
            ResultSet items = this.db.query(""
                + "SELECT * "
                + "FROM OrderItem "
                + "JOIN Orders ON orderitem_orderid = order_id "
                + "JOIN Product ON product_id = orderItem_productid "
                + "WHERE orderitem_orderid = " + order.getId());

            if (items != null) {
                while(items.next()) {
                    Product product = new Product(
                            items.getInt("product_id"),
                            items.getString("product_name"),
                            items.getFloat("product_purchasePrice"),
                            items.getFloat("product_salesPrice"),
                            items.getFloat("product_rentPrice"),
                            items.getString("product_country"),
                            items.getInt("product_minstock"));
                    OrderItem oi = new OrderItem(product, items.getInt("orderItem_quantity"));
                    orderItems.add(oi);
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(OrderDb.class.getName()).log(Level.SEVERE, null, ex);
        }

        return orderItems;
    }

    public ArrayList<Order> getAllOrders() {
        ArrayList<Order> orders = new ArrayList<Order>();
        try {
            ResultSet rs = this.db.query(""
                + "SELECT * "
                + "FROM Orders "
                + "JOIN Customers ON customer_id = order_customerid "
                + "JOIN Cities ON customer_zipcode = cities_zipcode"
                + "JOIN Employee ON employee_id = order_employeeid");

            if (rs != null) {
                while(rs.next()) {
                    Order order = new Order(
                            rs.getInt("order_id"),
                            rs.getInt("order_amount"),
                            rs.getFloat("order_totalPrice"),
                            rs.getInt("order_deliveryStatus"),
                            rs.getString("order_deliveryDate"),
                            rs.getString("order_date"));

                    Employee employee = new Employee(
                            rs.getInt("employee_id"),
                            rs.getString("employee_username"),
                            rs.getString("employee_name"));

                    Customer customer = new Customer(
                            rs.getInt("customer_id"),
                            rs.getString("customer_name"),
                            rs.getString("customer_address"),
                            rs.getInt("customer_zipcode"),
                            rs.getString("cities_name"),
                            rs.getLong("customer_phoneNo"));

                    order.setCustomer(customer);
                    order.setEmployee(employee);

                    order.setOrderItems(this.getOrderItems(order));

                    orders.add(order);
                }

            }
        } catch (SQLException ex) {
            Logger.getLogger(OrderDb.class.getName()).log(Level.SEVERE, null, ex);
        }

        return orders;
    }

    public int saveOrder(Order order) {
        int orderId = -1;
        int sqlSuccess = -1;
        int sqlSuccess2 = -1;

        // Indsætter i Orders-tabellen
        sqlSuccess = this.db.queryInsert(""
                + "INSERT INTO Orders ("
                + "order_customerid, order_employeeid, order_amount, order_totalPrice, order_deliveryStatus, order_deliveryDate, order_date"
                + ") VALUES ("
                    // Kunde ID
                    + order.getCustomer().getId() + ", "
                    // Medarbejder ID
                    + order.getEmployee().getId() + ", "
                    // Antal varer i hele ordren
                    + order.getAmount() + ", "
                    // Total pris på ordre
                    + order.getTotalPrice() + ", "
                    // Er ikke leveret endnu
                    + "0, "
                    // Levering om 5 dage
                    + "DATEADD(day, 5, GETDATE()),"
                    // Dato og tid lige nu
                    + "GETDATE()"
                + ");");

        if(sqlSuccess > 0) {
            orderId = this.db.getInsertId();
            ArrayList<OrderItem> orderItems = order.getOrderItems();
            String query = "";
            query += "INSERT INTO OrderItem (";
            query += "orderItem_orderid, orderItem_productid, orderItem_quantity";
            query += ") VALUES ";

            String queryValues = "";
            
            // Løber alle produkter igennem
            for(OrderItem oi : orderItems) {

                if(queryValues.length() > 0) {
                    // Tilføjer et komma hvis der har været værdier sat ind før denne
                    queryValues += ", ";
                }

                queryValues += "(";
                queryValues += orderId + ", " + oi.getProduct().getId() + ", " + oi.getQuantity();
                queryValues += ")";
            }

            query += queryValues + ";";

            // Indsætter i OrderItems-tabellen
            sqlSuccess2 = this.db.queryInsert(query);
        }

        if(orderId > 0 && sqlSuccess > 0 && sqlSuccess2 > 0) {
            return orderId;
        } else {
            return -1;
        }
    }

    public boolean updateOrder(Order order) {
        boolean success = false;
        
        int orderId = order.getId();
        ArrayList<OrderItem> orderItems = order.getOrderItems();
        
        /*
         * Start på opdatering af Orders-tabellen
         */
        String updateOrders = ""
                + "UPDATE Orders "
                + "SET "
                //+ "order_customerid, order_employeeid, order_amount, order_totalPrice, order_deliveryStatus, order_deliveryDate, order_date"
                    // Kunde ID
                    + "order_customerid = " + order.getCustomer().getId() + ", "
                    // Medarbejder ID
                    + "order_employeeid = " + order.getEmployee().getId() + ", "
                    // Antal varer i hele ordren
                    + "order_amount = " + order.getAmount() + ", "
                    // Total pris på ordre
                    + "order_totalPrice = " + order.getTotalPrice() + ", "
                    // Er ikke leveret endnu
                    + "order_deliveryStatus = " + order.getDeliveryStatus() + ", "
                    // Levering om 5 dage
                    + "order_deliveryDate = '" + order.getDeliveryDate() + "' "
                + "WHERE order_id = " + orderId + ";";
        /*
         * Slut på opdatering af Orders-tabellen
         */
         
        /*
         * Sletter produkter til ordren, for at kunne gemme evt. rettelser
         */
        String deleteProducts = "DELETE FROM OrderItem WHERE orderItem_orderid = " + orderId;

        /*
         * Start på opdatering af OrderItem
         */
        String insertProducts = "";
            insertProducts += "INSERT INTO OrderItem (";
            insertProducts += "orderItem_orderid, orderItem_productid, orderItem_quantity";
            insertProducts += ") VALUES ";

            String insertProductsValues = "";
            // Løber alle produkter igennem
            for(OrderItem oi : orderItems) {

                if(insertProductsValues.length() > 0) {
                    // Tilføjer et komma hvis der har været værdier sat ind før denne
                    insertProductsValues += ", ";
                }

                insertProductsValues += "(";
                insertProductsValues += orderId + ", " + oi.getProduct().getId() + ", " + oi.getQuantity();
                insertProductsValues += ")";
            }

            insertProducts += insertProductsValues + ";";
        /*
         * Slut på opdatering af OrderItem
         */

        // Ny transaction
        this.newTransaction();
        this.addStatement(updateOrders);
        this.addStatement(deleteProducts);
        this.addStatement(insertProducts);
        
        // Udfører transactionen, eller kører rollback ved fejl
        success = this.commitTransaction();

        
        return success;
    }

}
